[小ワザ] Amazon Athenaのviewで表示できるクエリ文字列をCLIとAWS SDK for JavaScriptで取得してみた

Toda Tomohiro

2020.12.16

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

こんにちは、コンサル部＠大阪オフィスのTodaです。

Amazon Athenaのviewで表示されるクエリ文字列をCLIとAWS SDK for JavaScriptで取得してみました。

やりたいこと

Amazon AthenaのviewクエリをCLI/AWS SDK for JavaScriptにて取得。

前提条件

PCにAWS CLIが導入されている。
JavaScriptにAWS Glueが利用可能なAWS SDK for JavaScriptが導入されている。

Amazon Athenaのviewクエリとは？

今回、Athenaの画面の左側にある[ビュー] > [クエリの表示/編集]で表示されるクエリ文字列を取得してみます。
調査をしたところAWS CLIのAthenaに関するコマンドでは該当の情報を取得できないがGlueのコマンドでビューの情報が取得できる事がわかりましたので試してみます。

Amazon Athenaのviewクエリ

AWS CLIを使ってやってみる

まずは glueのget-tablesを利用して情報を取得してみます。

aws glue get-tables --database-name [データベース]

(例: データベース:sampledb)
aws glue get-tables --database-name sampledb
{
    "TableList": [
        {
            "Name": "elb_logs",
            "DatabaseName": "sampledb",
            ** 一部省略 **
        },
        {
            "Name": "view-test",
            "DatabaseName": "sampledb",
            ** 一部省略 **
        },
        {
            "Name": "view-test2",
            "DatabaseName": "sampledb",
            ** 一部省略 **
        }
    ]
}

テーブル、ビューに関する情報が一覧で出力されました。
今回取得したいクエリ情報は一覧内の[ViewOriginalText]にbase64エンコードにて保存されている事がわかりましたので必要箇所を抽出するようにパイプをしていきます。パイプ時の処理は下記3点が必要です。

viewの名称で抽出
情報内のViewOriginalTextを抽出
必要のない文字列 [/* Presto View:] と [ */] 削除
base64デコード

aws glue get-tables --database-name [データベース] | jq -r '.TableList[] | select(.Name=="[ビュー名]") | .ViewOriginalText' | sed -e 's/\/\* Presto View: //' | sed -e 's/ \*\///' | base64 -d

(例: データベース:sampledb  ビュー:view-test2)
aws glue get-tables --database-name sampledb | jq -r '.TableList[] | select(.Name=="view-test2") | .ViewOriginalText' | sed -e 's/\/\* Presto View: //' | sed -e 's/ \*\///' | base64 -d
{
    "originalSql":"SELECT\n  \"request_ip\"\n, \"request_port\"\n, \"url\"\nFROM\n  elb_logs\n",
    "catalog":"awsdatacatalog",
    "schema":"sampledb",
    "columns":[{"name":"request_ip","type":"varchar"},{"name":"request_port","type":"integer"},{"name":"url","type":"varchar"}]
}

取得できた情報内の[originalSql]が対象の情報になります。
後はエスケープ文字を外し、上記に「 CREATE OR REPLACE VIEW [ビュー名] AS 」をつなげた文字列が [クエリの表示/編集] で表示されています。

AWS SDK for JavaScriptを使ってやってみる

取得できる事を確認できましたのでJavaScriptの方で同等の対応が可能か確認をします。
確認をしたところ Class: AWS.Glue に getTables 使える事がわかりましたので同等の処理をしてみます。

Class: AWS.Glue getTables
https://docs.aws.amazon.com/AWSJavaScriptSDK/latest/AWS/Glue.html#getTables-property

AWS SDK for JavaScriptの Class: AWS.Glue を利用した場合に、クラスが存在しないエラーが出る場合があります。発生した場合は AWS SDK for JavaScript でClass: AWS.Glueを含む物を下記サイトからダウンロードします。

Build your own version of the AWS SDK for JavaScript
https://sdk.amazonaws.com/builder/js/

var params = {
    DatabaseName: 'sampledb' // データベース名を指定
};
var glue = new AWS.Glue();
glue.getTables(params, function(err, data) {
    if (err) {
        console.log(err);
    }else{
        data.TableList.forEach(function( table ) {
            if(table.ViewOriginalText){
                /* ビューの情報はこちら */
                var original_text = table.ViewOriginalText;
                var original_text = original_text.replace( /\/\* Presto View\:/g , "");  // オリジナルテキストから必要のない文字列削除 (Presto View:)
                var original_text = original_text.replace( / \*\//g , "");               // オリジナルテキストから必要のない文字列削除 ( /*)
                console.log(table.Name);
                console.log(atob(original_text));                                        // base64デコード
            }else{
                /* テーブルの情報はこちら */
            }
        });
    }
});

指定のビューを抽出する事はできませんでしたが、一覧を取得して対象のビュー抽出、クエリ情報を取得する事ができました。

ブラウザで確認